Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
GO

/*Drop stored procedures if they exists */

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'crearTipoMaterial' and type = 'P')

BEGIN
    DROP PROCEDURE crearTipoMaterial
END

GO

/*Done droping stored procedures*/


CREATE PROCEDURE crearTipoMaterial ( @nombreTipoMaterial VARCHAR (40) ,
									 @idUnidadMedida INT ,	 
								     @outParam INT OUTPUT						
								)
AS
	DECLARE @out AS INT;
	DECLARE @ident AS INT;
	DECLARE @val AS VARCHAR(40);
	
	SET @ident = 0;
	SET @out = -1;
		
	BEGIN TRANSACTION;
	
		BEGIN TRY
			
			SELECT @ident = MAX(IDTIPOMATERIAL) FROM TIPO_MATERIAL;

			SET @ident = @ident + 1;
			
			SET @val = UPPER(SUBSTRING(@nombreTipoMaterial , 0 , 4)) + (CAST(@ident AS VARCHAR(10))) + (CAST(@idUnidadMedida AS VARCHAR(10)));
			
			INSERT INTO TIPO_MATERIAL ( IDUNIDADDEMEDIDA , NOMBRETIPOMATERIAL , SKUTIPOMATERIAL ) VALUES (@idUnidadMedida ,  @nombreTipoMaterial , @val);
			
			SET @out = 1;
			
			SET @outParam = @out;
			
			COMMIT TRANSACTION;
			 
			SELECT @outParam;
		
		END TRY
		
		BEGIN CATCH
		
			SET @outParam = @out;
			
			ROLLBACK TRANSACTION;
		
		END CATCH;